<?php if(!defined('FRAME')) exit('No direct script access allowed');
/**
 * 数据库单例
 * @author wisoch@hotmail.com
 *
 */
class Db {
	private $_config;
	private $_link;
	private $_alias;
	private $_execInfo = array();
	private $params = array();
	private $_debug = false;
	static private $_instance;
	
	const SQL_FETCH_MODE = PDO::FETCH_ASSOC;
    const SQL_NONE 	= null;
	const SQL_ONE 	= 'one';
	const SQL_ALL 	= 'all';

	/***** 单件模式 *****/
	public static function Instance($config = null) {
		if(!self::$_instance) self::$_instance = new self($config);
		return self::$_instance;
	}
	public function __construct() {
		$this->_config = Config::Instance()->get('db');
	}
	/***** 单件模式 *****/
	
	/**
	 * 连接数据库
	 * example: dbname.master dbname.slave dbname
	 * @param string $alias
	 */
	public function connect($alias) {
		if($this->_link[$alias]) {
			$this->_alias = $alias;
			return $this;
		}
		//连接数据库
		$aliasArr = explode('.', $alias);
		if(count($aliasArr) == 2) $config = $this->_config[$aliasArr[0]][$aliasArr[1]];
		else $config = $this->_config[$aliasArr[0]];

		try {
			$dsn = 'mysql:host='.$config['host'].';port='.$config['port'].';dbname='.$config['name'];
			$db = new PDO($dsn, $config['user'], $config['pass'], $config['opts']);
		} catch (PDOException $e) {
			//再次尝试连接
			try {
				$db = new PDO($dsn, $config['user'], $config['pass'], $config['opts']);
			} catch (PDOException $e) {
				//写连接失败日志
				$this->_alias = null;
				$message = sprintf("[RUNTIME ERROR:PDO]-> %s. DSN: %s.", $e->getMessage(), $dsn);
				Log::write($message, true, true);
				return false;
			}
		}
		$db->query('SET NAMES UTF8');
		$this->_alias = $alias;
		$this->_link[$alias] = $db;
		return $this;
	}
	/**
	 * 关闭连接
	 * @param unknown_type $alias
	 */
	public function close($alias) {
        $this->_link[$alias] = null;
    }
	/**
     * 返回最后一次插入ID
     * @see PDO::lastInsertId()
     */
    public function lastInsertId() {
    	return $this->_link[$this->_alias]->lastInsertId();
    }
	/**
	 * 获取PDO实例
	 * example: dbname.master dbname.slave
	 * @param string $alias
	 */
	public function Pdo($alias) {
		if(isset($this->_link[$this->_alias])) return $this->_link[$this->_alias];
		return $this->connect($alias);
	}
	/**
	 * 获取查询信息
	 */
	public function execInfo() {
		$num = count($this->_execInfo);
		$time = 0;
		if($num) {
			foreach($this->execInfo as $exec) {
				$time += $exec['time'];
			}
		}
		return array('num' => $num, 'time' => $time);
	}
	/**
	 * 插入记录
	 * @param unknown_type $data
	 * @param unknown_type $options
	 */
	public function insert($table, array $cols) {
		$colums = array();
        foreach ($cols as $key => $val){
            if(is_scalar($key)) { // 过滤非标量数据
                $colums[$key] = ':'.$key;
            } else {
            	trigger_error("PDO Error : Illegal filed", E_USER_ERROR);
            }
        }
        $sql   =  'INSERT INTO `' . $table . '` (`'.implode('`, `', array_keys($colums)).'`) VALUES ('.implode(', ', array_values($colums)).')';
        return $this->query($sql, $cols, self::SQL_NONE);
    }
    /**
     * 更新记录
     * @param array $cols
     */
    public function update($table, array $cols, $params = array()) {
    	$colums = array();
        foreach ($cols as $key => $val){
            if(is_scalar($key)) { // 过滤非标量数据
                $colums[] = '`'.$key.'`=:'.$key;
            } else {
            	trigger_error("PDO Error : Illegal filed", E_USER_ERROR);
            }
        }
        
        $sql   =  'UPDATE `' . $table . '` SET '.implode(', ', array_values($colums)) . $this->parseWhere($params);
        
        return $this->query($sql, array_merge($cols, $this->params), self::SQL_NONE);
    }
	/**
     * 替换记录
     * @param array $cols
     */
    public function replace($table, $cols, $params = array()) {
    	$colums = array();
        foreach ($cols as $key => $val){
            if(is_scalar($key)) { // 过滤非标量数据
                $colums[$key] = ':'.$key;
            } else {
            	trigger_error("PDO Error : Illegal filed", E_USER_ERROR);
            }
        }
        $sql   =  'REPLACE INTO `' . $table . '` (`'.implode('`, `', array_keys($colums)).'`) VALUES ('.implode(', ', array_values($colums)).')'. $this->parseWhere($params);
        return $this->query($sql, array_merge($cols, $this->params), self::SQL_NONE);
    }
	/**
     * 删除记录
     * @param array $cols
     */
    public function delete($table, $params = array()) {
    	$colums = array();
        $sql   =  'DELETE FROM `' . $table .'`'. $this->parseWhere($params);
        return $this->query($sql, $this->params, self::SQL_NONE);
    }
    /**
     * 简单查询
     * @param unknown_type $table
     * @param unknown_type $cols
     * @param unknown_type $params
     * @param unknown_type $orderby
     * @param unknown_type $limit
     */
    public function select($table, $cols = '*', $params = array(), $orderby = null, $limit = null) {
    	if(is_array($cols)) $query = array(sprintf("SELECT `%s` FROM `{$table}`", implode('`, `', $cols)));
		else $query = array(sprintf("SELECT %s FROM `{$table}`", $cols));
		
		$where = $this->parseWhere($params);
		if($where) $query[] = $where;

		if($orderby) $query[] = 'ORDER BY '.$orderby;

		if($limit) $query[] = 'LIMIT  '.$limit;
	
		$query = implode(' ', $query);

		//执行查询
		return $this->query($query, $this->params, self::SQL_ALL);
    }
    /**
     * 获取一条数据
     * @param unknown_type $table
     * @param unknown_type $cols
     * @param unknown_type $params
     */
    public function one($table, $cols = '*', $params = array()) {
    	if(is_array($cols)) $query = array(sprintf("SELECT `%s` FROM `{$table}`", implode('`, `', $cols)));
		else $query = array(sprintf("SELECT %s FROM `{$table}`", $cols));
		
		$where = $this->parseWhere($params);
		if($where) $query[] = $where;

		$query[] = 'LIMIT  0, 1';
	
		$query = implode(' ', $query);

		//执行查询
		return $this->query($query, $this->params, self::SQL_ONE);
    }
    /**
     * 分页获取数据
     * @param unknown_type $table
     * @param unknown_type $cols
     * @param unknown_type $params
     * @param unknown_type $orderby
     * @param unknown_type $page
     * @param unknown_type $num
     */
	public function page($table, $cols = array(), $params = array(), $orderby = '', $page = 1, $size = 20) {
		$offset = ($page-1) > 0? ($page-1)*$size: 0;
    	$limit = $offset .', '.$size;
    	return $this->select($table, $cols, $params, $orderby, $limit);
	}
    /**
     * 获取数量
     * @param unknown_type $table
     * @param unknown_type $params
     */
    public function count($table, $params = array()) {
    	if(empty($table)) return false;
		
		$query = array(sprintf("SELECT COUNT(1) AS count FROM `%s`", $table));
		$query[] = $this->parseWhere($params);
		$query = implode(' ', $query);

		//执行查询
		$result = $this->query($query, $this->params, self::SQL_ONE);
		return isset($result['count'])? (int) $result['count']: 'false';
    }
    /**
     * 执行SQL
     * @param unknown_type $sql
     * @param unknown_type $params
     * @param unknown_type $return
     * @param unknown_type $fetch_mode
     */
    public function query($sql, $params = array(), $return = self::SQL_NONE, $fetch_mode = self::SQL_FETCH_MODE) {
		if(!isset($this->_link[$this->_alias])) return false;
    	//预处理SQL
		try {
			$stmt = $this->_link[$this->_alias]->prepare($sql);
			if(!$stmt) {
				//出错写日志
				$error = sprintf("[PDO Error]: %s Query: %s", $this->_link[$this->_alias]->errorInfo(), $sql);
				Log::write($error, true, true);
				return false;
			}
		} catch (PDOException $e) {
			//出错写日志
			$error = sprintf("[PDO Error]: %s Query: %s", $e->getMessage(), $sql);
			Log::write($error, true, true);
			return false;
		}

		//执行查询
		try {
			$result = $stmt->execute($params);
			if($result === false) {
				$error = $stmt->errorInfo();
				$errmsg = sprintf("[PDO Error]: %s . Query: %s", implode(':', $error), $sql);
				Log::write($errmsg, true, true);
				return false;
			}
		}
		catch (PDOException $e) {
			//查询出错写日志
			$error = sprintf("[PDO Error]: %s Query: %s", $e->getMessage(), $sql);
			Log::write($error, true, true);
			return false;
		}
		$stmt->setFetchMode($fetch_mode);

		switch($return) {
			case self::SQL_ONE:
				return $stmt->fetch();
				break;
		  	case self::SQL_ALL:
				return $stmt->fetchAll();
				break;
		  	case self::SQL_NONE:
		  		default:
				return $result;
				break;
		}
    }
    /**
     * 解析where条件
     * @param unknown_type $params
     */
    public function parseWhere($params) {
    	$this->_init();
    	if(is_string($params)) return $params;
    	
    	$where = array();
    	foreach ($params as $key => $value) {
    		$pattern = '/(.*)\s(<|<\=|\!\=|>|>\=|in|like|notlike)$/i';
    		$comparison = '=';
    		//protected $comparison = array('eq'=>'=','neq'=>'!=','gt'=>'>','egt'=>'>=','lt'=>'<','elt'=>'<=','notlike'=>'NOT LIKE','like'=>'LIKE');
    		$filed = $key;
    		if(preg_match($pattern,$key, $matches)) {
    			$filed = $matches[1];
    			$comparison = $matches[2];
    		}
    		if(strtoupper($comparison) == 'IN') {
    			$where[] = '`' . $filed . '` IN (\'' . implode('\', \'', $value).'\')';
    		} else if(strtoupper($comparison) == 'LIKE') {
    			$where[] = '`' . $filed . '` LIKE :'.$filed;
    			$this->params[$filed] = $value;
    		} else if(strtoupper($comparison) == 'NOTLIKE') {
    			$where[] = '`' . $filed . '` NOT LIKE :'.$filed;
    			$this->params[$filed] = $value;
    		} else {
    			$where[] = '`' . $filed . '`'.$comparison.':' . $filed;
    			$this->params[$filed] = $value;
    		}
    	}
    	
    	return $where? ' WHERE '.implode(' AND ', $where): '';
    }
    /**
     * 初始化查询
     */
    private function _init() {
    	$this->params = array();
    	$this->_execInfo = '';
    }
}